<?php

/**
 * Capital Direct Lending (http://www.philbsd.com/cdl)
 *
 * Copyright (C) 2008 Philippine Business Solution Developers <http://www.philbsd.com>
 * All rights reserved.
 *
 *
 * LICENSE
 *
 * This source file is subject to the GNU General Public License v3 license
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://www.gnu.org/licenses/gpl-3.0.txt
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@philbsd.com so we can send you a copy immediately.
 *
 * @copyright  (c) 2008 Philippine Business Solution Developers. (http://www.philbsd.com)
 * @license    http://www.gnu.org/licenses/gpl-3.0.txt
 * @version    $Id: report.php 262 2008-05-15 13:48:58Z artiskool $
 */

if (!defined('BASEPATH')) exit('No direct script access allowed');

class Report extends SB_Controller
{
	public function __construct()
	{
		parent::__construct();
		$this->_data['menuReport'] = true;
        $this->load->model('Reports');
	}

	public function index()
	{
		header("Location: {$this->_data['baseUrl']}report/collection.html");
	}

	public function collection()
	{
        if (isset($_POST['due_date'])) {
            $date = $_POST['due_date'];
		    $objReport = new Reports();
		    $this->_data['records'] = $objReport->getCollection($date);
            $this->render('report/collection.shtml');
        } else {
            $objReport = new Reports();
		    $this->_data['records'] = $objReport->getCollection('');
            $this->render('report/collection.shtml');
        }

	}

	public function generateCollection()
	{
		$objReport = new Reports();
		//$records = $objReport->getAll();
		//$records = $objReport->getCollection();
		//echo "<pre>";
		//print_r($records);die();
	
		require_once 'PHPExcel.php';
		require_once 'PHPExcel/Calculation.php';
		require_once 'PHPExcel/Writer/Excel5.php';

		//echo date('H:i:s') . " List implemented functions\n";
		$objCalc = PHPExcel_Calculation::getInstance();
		$objCalc->listFunctionNames();

		// Create new PHPExcel object
		$objExcel = new PHPExcel();
		$objExcel->createSheet();
		$objExcel->createSheet();

		// Select sheet 0 (although sheet 0 is the default)
		$objExcel->setActiveSheetIndex(0);
		// Set sheet 0 title
		$objExcel->getActiveSheet()->setTitle('Collection Report');

		// Add data to sheet 0
		// these are the headers
		$objExcel->getActiveSheet()->setCellValue('A1', 'Collector Name');
		$objExcel->getActiveSheet()->setCellValue('A2', 'Date');
		$objExcel->getActiveSheet()->setCellValue('A5', 'First Name');
		$objExcel->getActiveSheet()->setCellValue('B5', 'Last Name');
		$objExcel->getActiveSheet()->setCellValue('C5', 'Address');
		$objExcel->getActiveSheet()->setCellValue('D5', 'Amount due');
		$objExcel->getActiveSheet()->setCellValue('E5', 'Past Due');
		$objExcel->getActiveSheet()->setCellValue('F5', 'Borrower ID#');
		$objExcel->getActiveSheet()->setCellValue('G5', 'Amount Paid');
		$objExcel->getActiveSheet()->setCellValue('H5', 'Signature');

		// put values into it
		$objExcel->getActiveSheet()->setCellValue('B1', 'no collector');
		$objExcel->getActiveSheet()->setCellValue('B2', date('Y-m-d'));

		$collections = $objReport->exportCollection();
		$collectionRow = 6;
		$totalDue = 0;
		$totalPaid = 0;
		foreach ($collections as $collection) {
			$objExcel->getActiveSheet()->setCellValue('A'.$collectionRow, $collection['Borrower']['Profile']['first_name']);
			$objExcel->getActiveSheet()->setCellValue('B'.$collectionRow, $collection['Borrower']['Profile']['last_name']);
			$objExcel->getActiveSheet()->setCellValue('C'.$collectionRow, "{$collection['Borrower']['Profile']['address']}, {$collection['Borrower']['Profile']['barangay']}, {$collection['Borrower']['Profile']['city']}, {$collection['Borrower']['Profile']['island']}");
			$objExcel->getActiveSheet()->setCellValue('D'.$collectionRow, $collection['amount_due']);
			$objExcel->getActiveSheet()->setCellValue('E'.$collectionRow, $collection['due_date']);
			$objExcel->getActiveSheet()->setCellValue('F'.$collectionRow, $collection['Borrower']['id']);
			$objExcel->getActiveSheet()->setCellValue('G'.$collectionRow, "");
			//$objExcel->getActiveSheet()->setCellValue('H5'.$row, 'Signature');
			$collectionRow++;
			$totalDue += $collection['amount_due'];
		}

		$objExcel->getActiveSheet()->getStyle('A5')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('B5')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('C5')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('D5')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('E5')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('F5')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('G5')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('H5')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12.86);
		$objExcel->getActiveSheet()->getColumnDimension('B')->setWidth(14.29);
		$objExcel->getActiveSheet()->getColumnDimension('C')->setWidth(24.43);
		$objExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12.14);
		$objExcel->getActiveSheet()->getColumnDimension('E')->setWidth(8.43);
		$objExcel->getActiveSheet()->getColumnDimension('F')->setWidth(11.71);
		$objExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12.29);
		$objExcel->getActiveSheet()->getColumnDimension('H')->setWidth(16.86);

		$collectionRow += 5;
		$objExcel->getActiveSheet()->setCellValue('D' . $collectionRow, $totalDue);
		$objExcel->getActiveSheet()->setCellValue('C' . $collectionRow++, 'Total Due');
		$objExcel->getActiveSheet()->setCellValue('D' . $collectionRow, '');
		$objExcel->getActiveSheet()->setCellValue('C' . $collectionRow++, 'Total Paid');
		$objExcel->getActiveSheet()->setCellValue('C' . $collectionRow++, 'Collector Signature');
		$objExcel->getActiveSheet()->setCellValue('C' . $collectionRow++, 'Supervisor Count');
		$objExcel->getActiveSheet()->setCellValue('C' . $collectionRow++, 'Supervisor Signature');

		$objWriter = new PHPExcel_Writer_Excel5($objExcel);
		$file = WWW_DIR . DS . 'data' . DS . 'Collection-report-' . date('Y-m-d-h-i-s-A') . '.xls';
		$objWriter->save($file);

		header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
		header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
		header ("Cache-Control: no-cache, must-revalidate");
		header ("Pragma: no-cache");
		header ("Content-type: application/x-msexcel");
		header ("Content-Disposition: attachment; filename=\"" . basename($file) . "\"" );
		header ("Content-Description: PHP/INTERBASE Generated Data" );
		readfile($file);
		unlink($file);
		exit;

	}

	public function pending_approval()
	{
		$objReport = new Reports();
		$this->_data['records'] = $objReport->getPendingApproval();
		$this->render('report/pending_approval.shtml');
	}

	public function generateApproval()
	{
		$objReport = new Reports();
		//$records = $objReport->getAll();
		//$records = $objReport->getCollection();
		//echo "<pre>";
		//print_r($records);die();
	
		require_once 'PHPExcel.php';
		require_once 'PHPExcel/Calculation.php';
		require_once 'PHPExcel/Writer/Excel5.php';

		//echo date('H:i:s') . " List implemented functions\n";
		$objCalc = PHPExcel_Calculation::getInstance();
		$objCalc->listFunctionNames();

		// Create new PHPExcel object
		$objExcel = new PHPExcel();
		$objExcel->createSheet();
		$objExcel->createSheet();

		// Select sheet 0
		$objExcel->setActiveSheetIndex(0);
		// Set sheet 1 title
		$objExcel->getActiveSheet()->setTitle('Pending Approval Report');
		// Add data to sheet 1
		$objExcel->getActiveSheet()->setCellValue('A1', 'Date');
		$objExcel->getActiveSheet()->setCellValue('B1', 'Borrower ID');
		$objExcel->getActiveSheet()->setCellValue('C1', 'First Name');
		$objExcel->getActiveSheet()->setCellValue('D1', 'Last Name');
		$objExcel->getActiveSheet()->setCellValue('E1', 'Barangay');
		$objExcel->getActiveSheet()->setCellValue('F1', 'Amount Requested');
		$objExcel->getActiveSheet()->setCellValue('G1', 'Approved Amount');
		$objExcel->getActiveSheet()->setCellValue('H1', 'Approved by');

		$approvals = $objReport->getPendingApproval();
		$approvalRow = 2;
		$totalApproval = 0;
		foreach ($approvals as $approval) {
			$objExcel->getActiveSheet()->setCellValue('A'.$approvalRow, $approval['application_date']);
			$objExcel->getActiveSheet()->setCellValue('B'.$approvalRow, $approval['Borrower']['id']);
			$objExcel->getActiveSheet()->setCellValue('C'.$approvalRow, $approval['Borrower']['Profile']['first_name']);
			$objExcel->getActiveSheet()->setCellValue('D'.$approvalRow, $approval['Borrower']['Profile']['last_name']);
			$objExcel->getActiveSheet()->setCellValue('E'.$approvalRow, $approval['Borrower']['Profile']['barangay']);
			$objExcel->getActiveSheet()->setCellValue('F'.$approvalRow, $approval['loan_amount']);
			$objExcel->getActiveSheet()->setCellValue('G'.$approvalRow, '');
			$objExcel->getActiveSheet()->setCellValue('H'.$approvalRow, '');
			$approvalRow++;
			$totalApproval += $approval['loan_amount'];
		}

		$objExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('E1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('G1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('H1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8.43);
		$objExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12.43);
		$objExcel->getActiveSheet()->getColumnDimension('C')->setWidth(11.71);
		$objExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12.71);
		$objExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12.71);
		$objExcel->getActiveSheet()->getColumnDimension('F')->setWidth(18.14);
		$objExcel->getActiveSheet()->getColumnDimension('G')->setWidth(18);
		$objExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12);

		$approvalRow += 5;
		$objExcel->getActiveSheet()->setCellValue('F' . $approvalRow, $totalApproval);
		$objExcel->getActiveSheet()->setCellValue('E' . $approvalRow++, 'total requested');


		$objWriter = new PHPExcel_Writer_Excel5($objExcel);
		$file = WWW_DIR . DS . 'data' . DS . 'Approval-report-' . date('Y-m-d-h-i-s-A') . '.xls';
		$objWriter->save($file);

		header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
		header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
		header ("Cache-Control: no-cache, must-revalidate");
		header ("Pragma: no-cache");
		header ("Content-type: application/x-msexcel");
		header ("Content-Disposition: attachment; filename=\"" . basename($file) . "\"" );
		header ("Content-Description: PHP/INTERBASE Generated Data" );
		readfile($file);
		unlink($file);
		exit;

	}

	public function pending_release()
	{
		$objReport = new Reports();
		$this->_data['records'] = $objReport->getPendingRelease();
		$this->render('report/pending_release.shtml');
	}

	public function generateRelease()
	{
		$objReport = new Reports();
		//$records = $objReport->getAll();
		//$records = $objReport->getCollection();
		//echo "<pre>";
		//print_r($records);die();
	
		require_once 'PHPExcel.php';
		require_once 'PHPExcel/Calculation.php';
		require_once 'PHPExcel/Writer/Excel5.php';

		//echo date('H:i:s') . " List implemented functions\n";
		$objCalc = PHPExcel_Calculation::getInstance();
		$objCalc->listFunctionNames();

		// Create new PHPExcel object
		$objExcel = new PHPExcel();
		$objExcel->createSheet();
		$objExcel->createSheet();

		// Select sheet 0
		$objExcel->setActiveSheetIndex(0);
		// Set sheet 0 title
		$objExcel->getActiveSheet()->setTitle('Pending Releases Report');
		// Add data to sheet 0
		$objExcel->getActiveSheet()->setCellValue('A1', 'Date');
		$objExcel->getActiveSheet()->setCellValue('B1', 'Borrower ID');
		$objExcel->getActiveSheet()->setCellValue('C1', 'First Name');
		$objExcel->getActiveSheet()->setCellValue('D1', 'Last Name');
		$objExcel->getActiveSheet()->setCellValue('E1', 'Amount Requested');
		$objExcel->getActiveSheet()->setCellValue('F1', 'Approved Amount');
		$objExcel->getActiveSheet()->setCellValue('G1', 'Check Number');

		$releases = $objReport->getPendingRelease();
		$releaseRow = 2;
		$totalReleased = 0;
		$totalApproved = 0;
		foreach ($releases as $release) {
			$objExcel->getActiveSheet()->setCellValue('A'.$releaseRow, $release['application_date']);
			$objExcel->getActiveSheet()->setCellValue('B'.$releaseRow, $release['Borrower']['id']);
			$objExcel->getActiveSheet()->setCellValue('C'.$releaseRow, $release['Borrower']['Profile']['first_name']);
			$objExcel->getActiveSheet()->setCellValue('D'.$releaseRow, $release['Borrower']['Profile']['last_name']);
			$objExcel->getActiveSheet()->setCellValue('E'.$releaseRow, $release['loan_amount']);
			$objExcel->getActiveSheet()->setCellValue('F'.$releaseRow, '');
			$objExcel->getActiveSheet()->setCellValue('G'.$releaseRow, '');
			$releaseRow++;
			$totalReleased += $release['loan_amount'];
		}

		$objExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('E1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('G1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getColumnDimension('A')->setWidth(14.43);
		$objExcel->getActiveSheet()->getColumnDimension('B')->setWidth(14.43);
		$objExcel->getActiveSheet()->getColumnDimension('C')->setWidth(14.43);
		$objExcel->getActiveSheet()->getColumnDimension('D')->setWidth(14.43);
		$objExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20.86);
		$objExcel->getActiveSheet()->getColumnDimension('F')->setWidth(17.71);
		$objExcel->getActiveSheet()->getColumnDimension('G')->setWidth(16.14);

		$releaseRow += 5;
		$objExcel->getActiveSheet()->setCellValue('E' . $releaseRow, $totalReleased);
		$objExcel->getActiveSheet()->setCellValue('D' . $releaseRow++, 'total requested');
		$objExcel->getActiveSheet()->setCellValue('E' . $releaseRow, '');
		$objExcel->getActiveSheet()->setCellValue('D' . $releaseRow++, 'total approved');

		$objWriter = new PHPExcel_Writer_Excel5($objExcel);
		$file = WWW_DIR . DS . 'data' . DS . 'Release-report-' . date('Y-m-d-h-i-s-A') . '.xls';
		$objWriter->save($file);

		header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
		header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
		header ("Cache-Control: no-cache, must-revalidate");
		header ("Pragma: no-cache");
		header ("Content-type: application/x-msexcel");
		header ("Content-Disposition: attachment; filename=\"" . basename($file) . "\"" );
		header ("Content-Description: PHP/INTERBASE Generated Data" );
		readfile($file);
		unlink($file);
		exit;

	}

	public function custom_report()
	{

		$loan_fields = array();
		$loan_fields['borrower_id'] = 'Borrower ID';
		$loan_fields['coborrower_id'] = 'Coborrower ID';
		$loan_fields['collector_id'] = 'Collector ID';
		$loan_fields['pn_signed_date'] = 'PN Signed Date';
		$loan_fields['surcharges'] = 'Surcharges';
		$loan_fields['status'] = 'Status';
		$loan_fields['loan_amount'] = 'Amount';
		$loan_fields['principal_amount'] = 'Principal Amount';
		$loan_fields['interest_amount'] = 'Interest Amount';
		$loan_fields['number_payments'] = 'Number Payments';
		$loan_fields['daily_payment'] = 'Daily Payment';
		$loan_fields['start_date'] = 'Start Date';
		$loan_fields['default_date'] = 'Default Date';
		$loan_fields['delinquent_date'] = 'Delinquent Date';
		$loan_fields['inrepayment_date'] = 'In Repayment Date';
		$loan_fields['release_pending_date'] = 'Release Pending Date';
		$loan_fields['inquiry_date'] = 'Inquiry Date';
		$loan_fields['application_date'] = 'Application Date';
		$loan_fields['credit_investigation_date'] = 'Credit Investigation Date';
		$loan_fields['paid_date'] = 'Paid Date';
		$loan_fields['payment_terms'] = 'Payment Terms';
		$loan_fields['payment_days'] = 'Payment Days';

		$profile_fields = array();
		$profile_fields['last_name'] = 'Last Name';
		$profile_fields['first_name'] = 'First Name';
		$profile_fields['address'] = 'Address';
		$profile_fields['barangay'] = 'Barangay';
		$profile_fields['city'] = 'City';
		$profile_fields['island'] = 'Island';
		$profile_fields['phone'] = 'Phone';
		$profile_fields['mobile'] = 'Mobile';
		$profile_fields['birthdate'] = 'Birthdate';
		$profile_fields['gender'] = 'Gender';
		$profile_fields['civil_status'] = 'Civil Status';

		$business_fields = array();
		$business_fields['id'] = 'Business ID';
		$business_fields['status_date'] = 'Status Date';
		$business_fields['name'] = 'Business Name';
		$business_fields['industry_type'] = 'Type of Industry';
		$business_fields['ave_daily_income'] = 'Average Daily Income';
		$business_fields['established_date'] = 'Date Established';
		$business_fields['days_operation'] = 'Days of Operation';

		$collateral_fields = array();
		$collateral_fields['id'] = 'Collateral ID';
		$collateral_fields['item_description'] = 'Item Description';
		$collateral_fields['manufacturer'] = 'Manufacturer';
		$collateral_fields['model'] = 'Model#';
		$collateral_fields['serial'] = 'Serial#';
		$collateral_fields['purchased_date'] = 'Date of Purchase';
		$collateral_fields['collateral_status'] = 'Collateral Status';

		$available_fields = array();

		// for loan info
		$available_fields['loan'] = $loan_fields;
		// for borrower info
		$available_fields['borrower'] = $profile_fields;
		// for coborrower info
		$available_fields['coborrower'] = $profile_fields;
		// for business info
		$available_fields['business'] = $business_fields;
		// for collateral info
		$available_fields['collateral'] = $collateral_fields;

		$this->_data['available_fields'] = $available_fields;

		if (isset($_POST['btnExport']) && isset($_POST['selected_fields'])) {
			$ok = false;
			if (!isset($_POST['selected_fields']) || !is_array($_POST['selected_fields'])) {
				$this->_data['msg'] = 'No selected field available. Please select at least one.';
			} else if (isset($_POST['date_from']) && !$_POST['date_from']) {
				$this->_data['msg'] = 'Date range is required.';
			} else if (isset($_POST['date_to']) && !$_POST['date_to']) {
				$this->_data['msg'] = 'Date range is required.';
			} else {
				$date_from_ok = false;
				$date_to_ok = false;
				$xf = explode('-', $_POST['date_from']);
				$xt = explode('-', $_POST['date_to']);
				if (count($xf) < 3 || count($xt) < 3) {
					$this->_data['msg'] = 'Invalid date format.';
				} else {
					if (!checkdate($xf[1], $xf[2], $xf[0])) {
						$this->_data['msg'] = 'Date is invalid.';
					} else {
						$date_from_ok = true;
					}
					if (!checkdate($xt[1], $xt[2], $xt[0])) {
						$this->_data['msg'] = 'Date is invalid.';
					} else {
						$date_to_ok = true;
					}
				}
				if ($date_from_ok && $date_to_ok) {
					if (strtotime($_POST['date_from']) > strtotime($_POST['date_to'])) {
						$this->_data['msg'] = 'Date from is greater than date to!';
					} else {
						$ok = true;
					}
				}
			}

			if ($ok) {

				$objReport = new Reports();
				$data['fields'] = $_POST['selected_fields'];
				$data['date_from'] = $_POST['date_from'];
				$data['date_to'] = $_POST['date_to'];
				$result = $objReport->getCustomReport($data);
				//echo '<pre>';
				//print_r($result);die();
				$this->_data['records'] = $result;

				if ($result) {
					$fields = $_POST['selected_fields'];
					//print_r($fields);

					require_once 'PHPExcel.php';
					require_once 'PHPExcel/Calculation.php';
					require_once 'PHPExcel/Writer/Excel5.php';

					$objExcel = new PHPExcel();
					//$objExcel->createSheet();

					// Select sheet 0 (although sheet 0 is the default)
					$objExcel->setActiveSheetIndex(0);
					// Set sheet 0 title
					$objExcel->getActiveSheet()->setTitle('Custom Report');

					// Add data to sheet 0
					// these are the headers
					$row = 1;
					$rowAsc = 65;
					$rowPrefix = 0;
					foreach ($fields as $field) {
						if ($rowAsc > 90) {
							$rowAsc = 65;
							$rowPrefix = ($rowPrefix == 0)? 65 : $rowPrefix + 1;
						}
						$chr = chr($rowAsc++);
						if ($rowPrefix > 0) {
							$chr = chr($rowPrefix) . $chr;
						}
						$x = explode('.', $field);
						$k = substr($x[0], 0, -1);
						$k_label = ucwords($k);
						$objExcel->getActiveSheet()->setCellValue($chr . $row, $k_label . ' ' . $available_fields[$k][$x[1]]);
						$objExcel->getActiveSheet()->getStyle($chr . $row)->getAlignment()->setWrapText(true);
					}

					// put values into it
					$row += 2;
					$status = array ("", "Inquiry", "Pending", "Approved", "Denied", "Released", "Closed");
					foreach ($this->_data['records'] as $record) {
						$rowAsc = 65;
						$rowPrefix = 0;
						foreach ($fields as $field) {
							if ($rowAsc > 90) {
								$rowAsc = 65;
								$rowPrefix = ($rowPrefix == 0)? 65 : $rowPrefix + 1;
							}
							$chr = chr($rowAsc++);
							if ($rowPrefix > 0) {
								$chr = chr($rowPrefix) . $chr;
							}
							$field = str_replace('.', '_', $field);
							// temporarily replace status number to human readable
							if ($field == 'status') {
								$record[$field] = $status[$record[$field]];
							}
							$objExcel->getActiveSheet()->setCellValue($chr . $row, $record[$field]);
						}
						$row++;
					}

					$objWriter = new PHPExcel_Writer_Excel5($objExcel);
					$file = WWW_DIR . DS . 'data' . DS . 'custom-report-' . date('Y-m-d-h-i-s-A') . '.xls';
					$objWriter->save($file);

					header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
					header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
					header ("Cache-Control: no-cache, must-revalidate");
					header ("Pragma: no-cache");
					header ("Content-type: application/x-msexcel");
					header ("Content-Disposition: attachment; filename=\"" . basename($file) . "\"" );
					header ("Content-Description: PHP/INTERBASE Generated Data" );
					readfile($file);
					unlink($file);
					exit;
				} else {
					$this->_data['msg'] = 'No record found!';
				}
			}
		} elseif (isset($_POST['btnSaveSession'])) {
			if (isset($_POST['selected_fields']) && is_array($_POST['selected_fields'])) {
				file_put_contents(APPPATH . 'tmp' . DS . 'cusrep.kat', implode("\n", $_POST['selected_fields']));
				$this->_data['msg'] = 'Successfully save assigned fields.';
			} else {
				$this->_data['msg'] = 'No selected field available. Please select at least one before you can save.';
			}
		} elseif (isset($_POST['btnLoadSession'])) {
			if (file_exists(APPPATH . 'tmp' . DS . 'cusrep.kat')) {
				$file_contents = file_get_contents(APPPATH . 'tmp' . DS . 'cusrep.kat');
				$this->_data['save_assigned_fields'] = explode("\n", $file_contents);
				$this->_data['msg'] = 'Save assigned fields successfully loaded.';
			} else {
				$this->_data['msg'] = 'No save assigned fields exists.';
			}
		}
        $this->render('report/custom_report.shtml');

	}

	public function generate()
	{
		$objReport = new Reports();
		//$records = $objReport->getAll();
		//$records = $objReport->getCollection();
		//echo "<pre>";
		//print_r($records);die();
	
		require_once 'PHPExcel.php';
		require_once 'PHPExcel/Calculation.php';
		require_once 'PHPExcel/Writer/Excel5.php';

		//echo date('H:i:s') . " List implemented functions\n";
		$objCalc = PHPExcel_Calculation::getInstance();
		$objCalc->listFunctionNames();

		// Create new PHPExcel object
		$objExcel = new PHPExcel();
		$objExcel->createSheet();
		$objExcel->createSheet();

		// Select sheet 0 (although sheet 0 is the default)
		$objExcel->setActiveSheetIndex(0);
		// Set sheet 0 title
		$objExcel->getActiveSheet()->setTitle('Collection Report');

		// Add data to sheet 0
		// these are the headers
		$objExcel->getActiveSheet()->setCellValue('A1', 'Collector Name');
		$objExcel->getActiveSheet()->setCellValue('A2', 'Date');
		$objExcel->getActiveSheet()->setCellValue('A5', 'First Name');
		$objExcel->getActiveSheet()->setCellValue('B5', 'Last Name');
		$objExcel->getActiveSheet()->setCellValue('C5', 'Address');
		$objExcel->getActiveSheet()->setCellValue('D5', 'Amount due');
		$objExcel->getActiveSheet()->setCellValue('E5', 'Past Due');
		$objExcel->getActiveSheet()->setCellValue('F5', 'Borrower ID#');
		$objExcel->getActiveSheet()->setCellValue('G5', 'Amount Paid');
		$objExcel->getActiveSheet()->setCellValue('H5', 'Signature');

		// put values into it
		$objExcel->getActiveSheet()->setCellValue('B1', 'no collector');
		$objExcel->getActiveSheet()->setCellValue('B2', date('n-j-Y'));

		$collections = $objReport->exportCollection();
		$collectionRow = 6;
		$totalDue = 0;
		$totalPaid = 0;
		foreach ($collections as $collection) {
			$objExcel->getActiveSheet()->setCellValue('A'.$collectionRow, $collection['Borrower']['Profile']['first_name']);
			$objExcel->getActiveSheet()->setCellValue('B'.$collectionRow, $collection['Borrower']['Profile']['last_name']);
			$objExcel->getActiveSheet()->setCellValue('C'.$collectionRow, "{$collection['Borrower']['Profile']['address']}, {$collection['Borrower']['Profile']['barangay']}, {$collection['Borrower']['Profile']['city']}, {$collection['Borrower']['Profile']['island']}");
			$objExcel->getActiveSheet()->setCellValue('D'.$collectionRow, $collection['amount_due']);
			$objExcel->getActiveSheet()->setCellValue('E'.$collectionRow, $collection['due_date']);
			$objExcel->getActiveSheet()->setCellValue('F'.$collectionRow, $collection['Borrower']['id']);
			$objExcel->getActiveSheet()->setCellValue('G'.$collectionRow, "");
			//$objExcel->getActiveSheet()->setCellValue('H5'.$row, 'Signature');
			$collectionRow++;
			$totalDue += $collection['amount_due'];
		}

		$objExcel->getActiveSheet()->getStyle('A5')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('B5')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('C5')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('D5')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('E5')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('F5')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('G5')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('H5')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12.86);
		$objExcel->getActiveSheet()->getColumnDimension('B')->setWidth(14.29);
		$objExcel->getActiveSheet()->getColumnDimension('C')->setWidth(24.43);
		$objExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12.14);
		$objExcel->getActiveSheet()->getColumnDimension('E')->setWidth(8.43);
		$objExcel->getActiveSheet()->getColumnDimension('F')->setWidth(11.71);
		$objExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12.29);
		$objExcel->getActiveSheet()->getColumnDimension('H')->setWidth(16.86);

		$collectionRow += 5;
		$objExcel->getActiveSheet()->setCellValue('D' . $collectionRow, $totalDue);
		$objExcel->getActiveSheet()->setCellValue('C' . $collectionRow++, 'Total Due');
		$objExcel->getActiveSheet()->setCellValue('D' . $collectionRow, '');
		$objExcel->getActiveSheet()->setCellValue('C' . $collectionRow++, 'Total Paid');
		$objExcel->getActiveSheet()->setCellValue('C' . $collectionRow++, 'Collector Signature');
		$objExcel->getActiveSheet()->setCellValue('C' . $collectionRow++, 'Supervisor Count');
		$objExcel->getActiveSheet()->setCellValue('C' . $collectionRow++, 'Supervisor Signature');

		// Select sheet 1
		$objExcel->setActiveSheetIndex(1);
		// Set sheet 1 title
		$objExcel->getActiveSheet()->setTitle('Pending Approval Report');
		// Add data to sheet 1
		$objExcel->getActiveSheet()->setCellValue('A1', 'Date');
		$objExcel->getActiveSheet()->setCellValue('B1', 'Borrower ID');
		$objExcel->getActiveSheet()->setCellValue('C1', 'First Name');
		$objExcel->getActiveSheet()->setCellValue('D1', 'Last Name');
		$objExcel->getActiveSheet()->setCellValue('E1', 'Barangay');
		$objExcel->getActiveSheet()->setCellValue('F1', 'Amount Requested');
		$objExcel->getActiveSheet()->setCellValue('G1', 'Approved Amount');
		$objExcel->getActiveSheet()->setCellValue('H1', 'Approved by');

		$approvals = $objReport->getPendingApproval();
		$approvalRow = 2;
		$totalApproval = 0;
		foreach ($approvals as $approval) {
			$objExcel->getActiveSheet()->setCellValue('A'.$approvalRow, $approval['application_date']);
			$objExcel->getActiveSheet()->setCellValue('B'.$approvalRow, $approval['Borrower']['id']);
			$objExcel->getActiveSheet()->setCellValue('C'.$approvalRow, $approval['Borrower']['Profile']['first_name']);
			$objExcel->getActiveSheet()->setCellValue('D'.$approvalRow, $approval['Borrower']['Profile']['last_name']);
			$objExcel->getActiveSheet()->setCellValue('E'.$approvalRow, $approval['Borrower']['Profile']['barangay']);
			$objExcel->getActiveSheet()->setCellValue('F'.$approvalRow, $approval['loan_amount']);
			$objExcel->getActiveSheet()->setCellValue('G'.$approvalRow, '');
			$objExcel->getActiveSheet()->setCellValue('H'.$approvalRow, '');
			$approvalRow++;
			$totalApproval += $approval['loan_amount'];
		}

		$objExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('E1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('G1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('H1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8.43);
		$objExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12.43);
		$objExcel->getActiveSheet()->getColumnDimension('C')->setWidth(11.71);
		$objExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12.71);
		$objExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12.71);
		$objExcel->getActiveSheet()->getColumnDimension('F')->setWidth(18.14);
		$objExcel->getActiveSheet()->getColumnDimension('G')->setWidth(18);
		$objExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12);

		$approvalRow += 5;
		$objExcel->getActiveSheet()->setCellValue('F' . $approvalRow, $totalApproval);
		$objExcel->getActiveSheet()->setCellValue('E' . $approvalRow++, 'total requested');

		// Select sheet 2
		$objExcel->setActiveSheetIndex(2);
		// Set sheet 2 title
		$objExcel->getActiveSheet()->setTitle('Pending Releases Report');
		// Add data to sheet 2
		$objExcel->getActiveSheet()->setCellValue('A1', 'Date');
		$objExcel->getActiveSheet()->setCellValue('B1', 'Borrower ID');
		$objExcel->getActiveSheet()->setCellValue('C1', 'First Name');
		$objExcel->getActiveSheet()->setCellValue('D1', 'Last Name');
		$objExcel->getActiveSheet()->setCellValue('E1', 'Amount Requested');
		$objExcel->getActiveSheet()->setCellValue('F1', 'Approved Amount');
		$objExcel->getActiveSheet()->setCellValue('G1', 'Check Number');

		$releases = $objReport->getPendingRelease();
		$releaseRow = 2;
		$totalReleased = 0;
		$totalApproved = 0;
		foreach ($releases as $release) {
			$objExcel->getActiveSheet()->setCellValue('A'.$releaseRow, $release['application_date']);
			$objExcel->getActiveSheet()->setCellValue('B'.$releaseRow, $release['Borrower']['id']);
			$objExcel->getActiveSheet()->setCellValue('C'.$releaseRow, $release['Borrower']['Profile']['first_name']);
			$objExcel->getActiveSheet()->setCellValue('D'.$releaseRow, $release['Borrower']['Profile']['last_name']);
			$objExcel->getActiveSheet()->setCellValue('E'.$releaseRow, $release['loan_amount']);
			$objExcel->getActiveSheet()->setCellValue('F'.$releaseRow, '');
			$objExcel->getActiveSheet()->setCellValue('G'.$releaseRow, '');
			$releaseRow++;
			$totalReleased += $release['loan_amount'];
		}

		$objExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('E1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getStyle('G1')->getAlignment()->setWrapText(true);
		$objExcel->getActiveSheet()->getColumnDimension('A')->setWidth(14.43);
		$objExcel->getActiveSheet()->getColumnDimension('B')->setWidth(14.43);
		$objExcel->getActiveSheet()->getColumnDimension('C')->setWidth(14.43);
		$objExcel->getActiveSheet()->getColumnDimension('D')->setWidth(14.43);
		$objExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20.86);
		$objExcel->getActiveSheet()->getColumnDimension('F')->setWidth(17.71);
		$objExcel->getActiveSheet()->getColumnDimension('G')->setWidth(16.14);

		$releaseRow += 5;
		$objExcel->getActiveSheet()->setCellValue('E' . $releaseRow, $totalReleased);
		$objExcel->getActiveSheet()->setCellValue('D' . $releaseRow++, 'total requested');
		$objExcel->getActiveSheet()->setCellValue('E' . $releaseRow, '');
		$objExcel->getActiveSheet()->setCellValue('D' . $releaseRow++, 'total approved');

		// Calculated data
		//echo 'Value of B14 [=COUNT(B2:B12)]: ' . $objExcel->getActiveSheet()->getCell('B14')->getCalculatedValue() . "\r\n";

		$objWriter = new PHPExcel_Writer_Excel5($objExcel);
		$file = WWW_DIR . DS . 'data' . DS . 'report-' . date('Y-m-d-h-i-s-A') . '.xls';
		$objWriter->save($file);

		header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
		header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
		header ("Cache-Control: no-cache, must-revalidate");
		header ("Pragma: no-cache");
		header ("Content-type: application/x-msexcel");
		header ("Content-Disposition: attachment; filename=\"" . basename($file) . "\"" );
		header ("Content-Description: PHP/INTERBASE Generated Data" );
		readfile($file);
		unlink($file);
		exit;

	}

}
